package com.thinkit.bigdata.core.util.excel;

import com.google.common.base.Strings;
import com.thinkit.bigdata.core.util.DateFormatUtil;
import com.thinkit.bigdata.web.coremet.ExcelBean;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by BanAiQin on 2017/3/28.
 * Excel导出的公共方法
 * Excel读取的公共方法
 */
public class XlsUtil {

    //获取xls标题样式
    private static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
        //设置标题样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();//创建样式对象
//        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//水平居中
//        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        //设置字体
        HSSFFont titleFont = workbook.createFont();//创建字体对象
        titleFont.setFontHeightInPoints((short) 15);//设置字体大小
//        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//设置粗体
        titleFont.setFontName("黑体");//设置黑体
        titleStyle.setFont(titleFont);
        return titleStyle;
    }

    //获取表文字体
    private static HSSFFont getTableFont(HSSFWorkbook workbook) {
        //设置表文字体
        HSSFFont tableFont = workbook.createFont();
        tableFont.setFontHeightInPoints((short) 12);//设置字体大小
        tableFont.setFontName("宋体");//设置为黑体字
        return tableFont;
    }

    //获取列标题样式
    private static HSSFCellStyle getHeadStyle(HSSFWorkbook workbook) {
        HSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setBorderBottom(BorderStyle.THIN);//设置单元格底部边框样式
        headStyle.setBorderTop(BorderStyle.THIN);//设置单元格顶部边框样式
        headStyle.setBorderLeft(BorderStyle.THIN);//设置单元格左边框样式
        headStyle.setBorderRight(BorderStyle.THIN);//设置单元格右边框样式
//        headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont tableFont = getTableFont(workbook);
        headStyle.setFont(tableFont);
        return headStyle;
    }

    //获取表文样式
    public static HSSFCellStyle getTableStyle(HSSFWorkbook workbook) {
        //设置表文样式
        HSSFCellStyle tableStyle = workbook.createCellStyle();
        tableStyle.setBorderBottom(BorderStyle.THIN);//设置单元格底部边框样式
        tableStyle.setBorderTop(BorderStyle.THIN);//设置单元格顶部边框样式
        tableStyle.setBorderLeft(BorderStyle.THIN);//设置单元格左边框样式
        tableStyle.setBorderRight(BorderStyle.THIN);//设置单元格右边框样式
//        tableStyle.setWrapText(true);//自动换行
//        tableStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
//        tableStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
        //设置表文字体
        HSSFFont tableFont = getTableFont(workbook);
        tableStyle.setFont(tableFont);
        return tableStyle;
    }

    //设置表格的标题和列标题
    public static void setTableTitleHeader(HSSFWorkbook workbook, HSSFSheet sheet, List<String> header, String title) {
        //设置样式
        HSSFCellStyle titleStyle = getTitleStyle(workbook);//创建样式对象
        int rHeight = 2;//标题所占的高度
        int rowSt = 0;//当前行位置
        //合并标题单元格
        int cnum = header.size() - 1;
        sheet.addMergedRegion(new CellRangeAddress(rowSt, rowSt + rHeight - 1, 0, cnum));//第0行至第1行，第0列至第cnum列
        HSSFRow row = sheet.createRow(rowSt);
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(titleStyle);
        String[] titles = title.split("#");
        cell.setCellValue(new HSSFRichTextString(titles[0]));
        rowSt += rHeight;
        if (titles.length == 2) {
            //包含副标题
            sheet.addMergedRegion(new CellRangeAddress(rowSt, rowSt + rHeight - 1, 0, cnum));//第0行至第1行，第0列至第cnum列
            row = sheet.createRow(rowSt);
            cell = row.createCell(0);
            cell.setCellStyle(titleStyle);
            cell.setCellValue(new HSSFRichTextString(titles[1]));
            rowSt += rHeight;
        }

        //设置列标题样式
        HSSFCellStyle headStyle = getHeadStyle(workbook);

        //添加列标题
        HSSFRow row2 = sheet.createRow(rowSt);
        HSSFCell cell2;
        for (int i = 0; i <= cnum; i++) {
            cell2 = row2.createCell(i);
            cell2.setCellStyle(headStyle);
            cell2.setCellValue(new HSSFRichTextString(header.get(i)));
        }
    }

    public static void main(String[] args) throws Exception {
        FileInputStream is = new FileInputStream("D:/1.xls");
        POIFSFileSystem ts = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(ts);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        for (int i = 0; sheet.getRow(i) != null; i++) {
            row = sheet.getRow(i);
            //循环获取一行中的每一列
            for (int j = 0; row.getCell(j) != null; j++) {
                System.out.print(row.getCell(j) + " ");
            }
            System.out.println();
        }
        is.close();
    }

    /**
     * 读取后缀为xls的excel文件的数据
     *
     * @param path -
     * @return List<StudentBean>
     * @author zhang 2015-08-18 00:10
     */
    public static List<ExcelBean> readXls(String path, int cellNum) {

        HSSFWorkbook hssfWorkbook = null;
        try {
            InputStream is = new FileInputStream(path);
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        ExcelBean excelBean;
        List<ExcelBean> list = new ArrayList<>();
        if (hssfWorkbook != null) {
            // Read the Sheet
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                // Read the Row
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow != null) {
                        excelBean = new ExcelBean();
                        excelBean.setXssCell1(getValue(hssfRow.getCell(0)));
                        if (hssfRow.getCell(1) != null && cellNum == 2) {
                            excelBean.setXssCell2(getValue(hssfRow.getCell(1)));
                        }
                        list.add(excelBean);
                    }
                }
            }
        }
        return list;
    }

    /**
     * 读取后缀为xlsx的excel文件的数据
     *
     * @param path -
     * @return List<StudentBean>
     * @author zhang 2015-08-18 00:08
     */
    public static List<ExcelBean> readXlsx(String path, int cellNum) {

        XSSFWorkbook xssfWorkbook = null;
        try {
            InputStream is = new FileInputStream(path);
            xssfWorkbook = new XSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        ExcelBean excelBean;
        List<ExcelBean> list = new ArrayList<>();
        if (xssfWorkbook != null) {
            // Read the Sheet
            for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
                if (xssfSheet == null) {
                    continue;
                }
                // Read the Row
                for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if (xssfRow != null) {
                        excelBean = new ExcelBean();
                        excelBean.setXssCell1(getValue(xssfRow.getCell(0)));
                        if (cellNum == 2 && xssfRow.getCell(1) != null) {
                            excelBean.setXssCell2(getValue(xssfRow.getCell(1)));
                        }
                        list.add(excelBean);
                    }
                }
            }
        }
        return list;
    }

    /**
     * @param path    -
     * @param cellNum -
     * @return -
     */
    public static List<ExcelBean> redTxt(String path, int cellNum) throws IOException {
        List<ExcelBean> list = new ArrayList<>();
        //BufferedReader是可以按行读取文件
        FileInputStream inputStream = new FileInputStream(path);
        BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8));
        String str;
        int i1 = 0;
        while ((str = bufferedReader.readLine()) != null) {
            if (i1 == 0) {
                i1++;
                continue;
            }
            String[] strings = str.split("\\|");
            ExcelBean excelBean = new ExcelBean();
            excelBean.setXssCell1(strings[0]);
            if (cellNum == 2 && strings.length == 2) {
                excelBean.setXssCell2(strings[1]);
            }
            list.add(excelBean);
        }
        //close
        inputStream.close();
        bufferedReader.close();
        return list;
    }

    /**
     * 获取文件扩展名
     *
     * @param path -
     * @return String
     * @author zhang 2015-08-17 23:26
     */
    public static String getExt(String path) {
        if (path == null || path.equals("") || !path.contains(".")) {
            return null;
        } else {
            return path.substring(path.lastIndexOf(".") + 1, path.length());
        }
    }

    /**
     * 判断后缀为xlsx的excel文件的数据类型
     *
     * @param xssfRow -
     * @return String
     * @author zhang 2015-08-18 00:12
     */
    @SuppressWarnings("static-access")
    private static String getValue(XSSFCell xssfRow) {
        return String.valueOf(xssfRow.getStringCellValue());
    }

    /**
     * 判断后缀为xls的excel文件的数据类型
     *
     * @param hssfCell -
     * @return String
     * @author zhang 2015-08-18 00:12
     */
    @SuppressWarnings("static-access")
    private static String getValue(HSSFCell hssfCell) {
//        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
//            return String.valueOf(hssfCell.getBooleanCellValue());
//        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
//            Double b = hssfCell.getNumericCellValue();
//            if (b != null) {
//                String f = df.format(b);
//                return f; //不用科学计数法表示的数字
//            } else {
//                return String.valueOf(hssfCell.getNumericCellValue());
//            }
//        } else {
            return String.valueOf(hssfCell.getStringCellValue());
//        }
    }

    public static void exportUser(
            List<Map<String, Object>> userList, String headerStr,
            String filePath, HttpServletResponse response, String type
    ) throws IOException {
        //设置标题和列名称
        String[] header = {"area", "groupname", "grouptype", "username", "rolename", "realname", "account", "mobileno", "idcard", "email", "workip"};
        if (!Strings.isNullOrEmpty(headerStr)) {
            header = headerStr.split(",");
        }

        Map<String, String> headerMap = new HashMap<>();
        for (String str : header) {
            headerMap.put(str, str);
        }

        List<String> headerList =  formatStr(header);

        String title = "用户信息列表";

        List<Map<String, String>> exportDatas = new ArrayList<>();

        //将查询结果，存放进列值
        for (Map<String, Object> userMap : userList) {
            Map<String,String> map=new HashMap<>();
            if (!Strings.isNullOrEmpty(headerMap.get("area"))) {
                if (userMap.get("area") == null) {
                    map.put("area", "");
                } else {
                    map.put("area", userMap.get("area").toString());
                }
            }
            if (!Strings.isNullOrEmpty(headerMap.get("groupname"))) {
                if (userMap.get("groupname") == null) {
                    map.put("groupname", "");
                } else {
                    map.put("groupname", userMap.get("groupname").toString());
                }
            }
            if (!Strings.isNullOrEmpty(headerMap.get("grouptype"))) {
                if (userMap.get("grouptype") == null) {
                    map.put("grouptype", "");
                } else {
                    map.put("grouptype", userMap.get("grouptype").toString());
                }
            }
            if (!Strings.isNullOrEmpty(headerMap.get("rolename"))) {
                if (userMap.get("rolename") == null) {
                    map.put("rolename", "");
                } else {
                    map.put("rolename", userMap.get("rolename").toString());
                }
            }
            if (!Strings.isNullOrEmpty(headerMap.get("username"))) {
                if (userMap.get("username") == null) {
                    map.put("username", "");
                } else {
                    map.put("username", userMap.get("username").toString());
                }
            }
            if (!Strings.isNullOrEmpty(headerMap.get("realname"))) {
                if (userMap.get("realname") == null) {
                    map.put("realname", "");
                } else {
                    map.put("realname", userMap.get("realname").toString());
                }
            }
            if (!Strings.isNullOrEmpty(headerMap.get("account"))) {
                if (userMap.get("account") == null) {
                    map.put("account", "");
                } else {
                    map.put("account", userMap.get("account").toString());
                }
            }
            if (!Strings.isNullOrEmpty(headerMap.get("mobileno"))) {
                if (userMap.get("mobileno") == null) {
                    map.put("mobileno", "");
                } else {
                    map.put("mobileno", userMap.get("mobileno").toString());
                }
            }
            if (!Strings.isNullOrEmpty(headerMap.get("idcard"))) {
                if (userMap.get("idcard") == null) {
                    map.put("idcard", "");
                } else {
                    map.put("idcard", userMap.get("idcard").toString());
                }
            }
            if (!Strings.isNullOrEmpty(headerMap.get("email"))) {
                if (userMap.get("email") == null) {
                    map.put("email", "");
                } else {
                    map.put("email", userMap.get("email").toString());
                }
            }
            if (!Strings.isNullOrEmpty(headerMap.get("workip"))) {
                if (userMap.get("workip") == null) {
                    map.put("workip", "");
                } else {
                    map.put("workip", userMap.get("workip").toString());
                }
            }
            exportDatas.add(map);
        }
        ex(headerList, header, title, exportDatas, filePath, response, type);
    }

    private static List<String> formatStr(String[] header) {
        List<String> headers = new ArrayList<>();
        for (String aHeader : header) {
            String head = "";
            switch (aHeader) {
                case "area":
                    head = "省份";
                    break;
                case "groupname":
                    head = "部门(班组)";
                    break;
                case "grouptype":
                    head = "类型";
                    break;
                case "rolename":
                    head = "角色";
                    break;
                case "username":
                    head = "账号";
                    break;
                case "realname":
                    head = "姓名";
                    break;
                case "account":
                    head = "工号";
                    break;
                case "mobileno":
                    head = "手机号";
                    break;
                case "idcard":
                    head = "身份证号";
                    break;
                case "email":
                    head = "邮箱";
                    break;
                case "workip":
                    head = "办公网出口ip";
                    break;
            }
            headers.add(head);
        }
        return headers;
    }

    private static void ex(
            List<String> headerList, String[] header, String title, List<Map<String, String>> exportDatas,
            String filePath, HttpServletResponse response, String type
    ) throws IOException {
        //生成excel
        HSSFWorkbook workbook = new HSSFWorkbook();//创建一个xls文档对象
        HSSFSheet sheet = workbook.createSheet();//创建一个工作薄对象

        XlsUtil.setTableTitleHeader(workbook, sheet, headerList, title);
        //获取表文样式
        HSSFCellStyle tableStyle = XlsUtil.getTableStyle(workbook);


        HSSFRow sheetRow = sheet.createRow(0);
        HSSFCell cell;
        for (int i = 0; i < headerList.size(); i++) {
            cell = sheetRow.createCell(i);
            cell.setCellValue(headerList.get(i));
            cell.setCellStyle(tableStyle);
        }

        HSSFRow row2;
        int rows = 3;
        for (Map<String, String> data : exportDatas) {
            row2 = sheet.createRow(rows++);
            int initCellNo = 0;
            for (String key : header) {
                Object object = data.get(key);
                if (object == null) {
                    row2.createCell(initCellNo).setCellValue("");
                } else {
                    row2.createCell(initCellNo).setCellValue(String.valueOf(object));
                }
                initCellNo++;
            }
        }
        for (int i = 0; i < header.length; i++) {
            sheet.autoSizeColumn((short) i, true);//自动调整列宽
        }

        if ("noMi".equals(type)) {
            //输出文件名
            String fileName1 = title + "用户信息表_" + DateFormatUtil.getNowTime() + ".xls";
            // 清空response
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName1.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));  //转码之后下载的文件名不会出现中文乱码
            workbook.write(response.getOutputStream());
        } else {
            // 清空response
            OutputStream out = new FileOutputStream(filePath);
            workbook.write(out);
        }
    }

}
